package cn.edu.sdjzu.cs.dingcan.dao;

import cn.edu.sdjzu.cs.dingcan.entity.Customer;
import cn.edu.sdjzu.cs.dingcan.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class CustomerDao {
    private final QueryRunner queryRunner;
    private final ResultSetHandler<Customer> customerResultSetHandler = new ResultSetHandler<Customer>() {
        @Override
        public Customer handle(ResultSet rs) throws SQLException {
            if(!rs.next()) {
                return null;
            }
            Customer customer = new Customer();
            customer.setId(rs.getLong("id"));
            customer.setUsername(rs.getString("username"));
            customer.setPassword(rs.getString("password"));
            customer.setRealName(rs.getString("real_name"));
            customer.setPhoneNumber(rs.getString("phone_number"));
            customer.setEmail(rs.getString("email"));
            customer.setDeliveryAddress(rs.getString("delivery_address"));
            customer.setStatus(Customer.CustomerStatus.values()[rs.getInt("status")]);

            return customer;
        }
    };
    private final ResultSetHandler<List<Customer>> customerListResultSetHandler = new ResultSetHandler<List<Customer>>() {
        @Override
        public List<Customer> handle(ResultSet rs) throws SQLException {
            List<Customer> result = new ArrayList<>();
            Customer customer;
            do {
                customer = CustomerDao.this.customerResultSetHandler.handle(rs);
                if(customer != null) {
                    result.add(customer);
                }
            } while(customer != null);
            return result;
        }
    };
    private static final String INSERT_SQL =
            "INSERT INTO customer (real_name, username, phone_number, email, delivery_address, password, status) " +
            " VALUES(?, ?, ?, ?, ?, ?, ?)";
    private static final String UPDATE_SQL =
            "UPDATE customer " +
            "   SET real_name = ?, phone_number = ?, email = ?, delivery_address = ?, password = ?, status = ? " +
            " WHERE id = ?";
    private static final String GET_BY_ID_SQL =
            "SELECT * FROM customer WHERE id = ?";
    private static final String GET_BY_USERNAME =
            "SELECT * FROM customer WHERE username = ?";
    private static final String GET_ALL_SQL =
            "SELECT * FROM customer";
    private static final String UPDATE_LAST_LOGIN_SQL =
            "UPDATE customer SET last_login_time = CURRENT_TIMESTAMP() WHERE username = ?";

    public CustomerDao() {
        this.queryRunner = JdbcUtils.getQueryRunner();
    }

    public Long add(Customer customer) throws SQLException {
        return this.queryRunner.insert(INSERT_SQL, new ScalarHandler<Long>(), customer.getRealName(), customer.getUsername(),
                customer.getPhoneNumber(), customer.getEmail(), customer.getDeliveryAddress(), customer.getPassword(),
                customer.getStatus().ordinal());
    }

    public void update(Customer customer) throws SQLException {
        this.queryRunner.update(UPDATE_SQL, customer.getRealName(), customer.getPhoneNumber(), customer.getEmail(),
                customer.getDeliveryAddress(), customer.getPassword(), customer.getStatus().ordinal(), customer.getId());
    }

    public void updateLastLoginTime(String username) throws SQLException {
        this.queryRunner.update(UPDATE_LAST_LOGIN_SQL, username);
    }

    public Customer getCustomer(Long customerId) throws SQLException {
        return this.queryRunner.query(GET_BY_ID_SQL, customerResultSetHandler, customerId);
    }

    public Customer getCustomer(String username) throws SQLException {
        return this.queryRunner.query(GET_BY_USERNAME, customerResultSetHandler, username);
    }
    public List<Customer> getCustomers() throws SQLException {
        return this.queryRunner.query(GET_ALL_SQL, customerListResultSetHandler);
    }
}
